﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Tab2Sql.Classes.db
{
    /// <summary>
    /// "Refactoring" Class. 
    /// Very early version.
    /// 
    /// So far:
    /// 
    /// 1. Convert multiple updates into one update.
    /// </summary>
    public class sqlTransform
    {
        /*
rules
-----
	
Assuming statements are of form:
	
UPDATE x SET y = z
WHERE a = b
	
UPDATE x SET y = 2*z
WHERE a = c
	
1. First instance of 
    UPDATE x SET y = 
Is taken as the statement and is henceforth the preamble.
	
2. All subsequent instances of "UPDATE x  " are removed.
	
3. All instances of "SET y = z" are replaced by "THEN z"
	
4. All instances of "WHERE a = " are replaced by "WHEN a = "
	
5. Statement order is arranged :
    1. PREAMBLE
    2. "CASE"
    3. WHEN/THEN Statements
    4. "ELSE y"
    6. "END"
    --FIN
		
    So :
    1. 	UPDATE x SET y = 	
    2.	CASE
    3.		WHEN a = b	
            THEN z
            WHEN a = c
            THEN 2*z
    4.	ELSE y
    5.	END
    */
        /// <summary>
        ///
        /// </summary>
        public string OutStatement { get; set; }
        /// <summary>
        ///
        /// </summary>
        public StringBuilder OutStatementB { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string SqlStatement { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string UpdateTable { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string UpdateColumn { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string WhereStatement { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string NewValue { get; set; }
        Dictionary<int, string> NewValues;
        Dictionary<int, string> WhereStatements;
        /// <summary>
        ///
        /// </summary>
        /// <param name="inString">
        /// A <see cref="System.String"/>
        /// </param>
        public sqlTransform(string inString)
        {
            NewValues = new Dictionary<int, string>();
            WhereStatements = new Dictionary<int, string>();
            
            SqlStatement = inString;
            OutStatementB = new StringBuilder();
            UpdateTable = "";
            UpdateColumn = "";
            NewValue = "";
            WhereStatement = "";
            string pattern1 = @"UPDATE\s+(.*)\s+SET\s+(.*)=\s*(.*)\s+WHERE\s+(.*)";
            Regex r = new Regex(pattern1,RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);
            Match m = r.Match(SqlStatement);
            while (m.Success)
            {
               
                for (int i = 1; i <= m.Groups.Count; i++)
                {
                    Group g = m.Groups[i];
                    
                    CaptureCollection cc = g.Captures;
                    for (int j = 0; j < cc.Count; j++)
                    {
                        Capture c = cc[j];
                        if (i == 1) { UpdateTable = c.ToString(); }
                        if (i == 2) { UpdateColumn = c.ToString(); }
                        if (i == 3)
                        {
                            int n2 = NewValues.Count;
                            n2++;
                            NewValue = c.ToString().Replace(";", String.Empty);
            
                            NewValues.Add(n2, NewValue);
                        }
                        if (i == 4)
                        {
                            int n = WhereStatements.Count;
                            n++;
                            WhereStatement = c.ToString().Replace(";", String.Empty);
                         
                            WhereStatements.Add(n, WhereStatement);
                        }
                    }
                }
                m = m.NextMatch();
            }


            produceOutput();

        }




        private void produceOutput()
        {
            int longestLine=0;
            int currentLine=0;
            string thisLine = "UPDATE " + UpdateTable;
            OutStatementB.AppendLine(thisLine);
            string root= "SET [" + UpdateColumn.Trim() + "] =";
            int baseLength = root.Length;
            string indent = DGen.DText.Replicate(" ",baseLength);
            thisLine = root;
            OutStatementB.AppendLine(thisLine);
            thisLine = indent+"CASE";
            OutStatementB.AppendLine(thisLine);
            foreach (var dic in WhereStatements)
            {
                if (dic.Value.ToString().Length > longestLine)
                {
                    longestLine=dic.Value.ToString().Length;
                }
            }
            for (int i = 0; i < WhereStatements.Count; i++)
			{
                currentLine=0;
			    string foo;
                string foo2;
                if (WhereStatements.TryGetValue(i, out foo))
                {
                    thisLine=indent+"\tWHEN "+foo.Replace(""+UpdateColumn.Trim()+" "," ["+UpdateColumn.Trim()+"] ");
                   currentLine = foo.Length;
                }
                if (NewValues.TryGetValue(i, out foo2))
                {
                    if (longestLine>currentLine)
                    {
                        thisLine+=DGen.DText.Replicate(" ",longestLine-currentLine);
                    }
                    thisLine+= " THEN "+foo2;
                    OutStatementB.AppendLine(thisLine);
                }

			}
            thisLine = indent + "\tELSE [" + UpdateColumn.Trim() + "]";
            OutStatementB.AppendLine(thisLine);
            thisLine = indent+"END";
            OutStatementB.AppendLine(thisLine);
            OutStatement = OutStatementB.ToString();
        }
    }







}
